package daoImp;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.Map;

import util.DateUtil;

import dao.TweetEmotionDAO;


public class TweetEmotionDAOImp implements TweetEmotionDAO{
	private static TweetEmotionDAOImp uniqueInstance = new TweetEmotionDAOImp();
	private ConnectionPool pool = ConnectionPool.getInstance();
	public static TweetEmotionDAOImp getInstance() {
		return uniqueInstance;
	}
	private TweetEmotionDAOImp() {
	}
	
	public void updateSentimentFrequency(Map<Integer, Integer> sentimentMap, String trend, Date date) {
		String sql = null;
		String dateStr = DateUtil.getNewsDateStr(date);
		
		try {
			Connection connection;
			try {
				connection = pool.getConnection();
			} catch (Exception e) {
				return;
			}
			Statement stmt = connection.createStatement();
			trend = trend.replaceAll("'", "\\\\'").replaceAll("\\)", "\\\\)").replaceAll("\\(", "\\\\(");
			sql = "select count(*) from tweetemotions where trend = '" + trend + "' and date = '" + dateStr + "';";
			ResultSet rs = null;
			int count = 0;
			try{
				rs = stmt.executeQuery(sql);
				while (rs.next()) {
					count = rs.getInt(1);
				}
			}finally{
				rs.close();
			}
			
			if(count > 0){
				if(sentimentMap.containsKey(1)){
					sql  = "update tweetemotions set positive = positive + " + sentimentMap.get(1) + " where trend = '" + trend + "' and date = '" + dateStr + "';";
					stmt.execute(sql);
				}
				if(sentimentMap.containsKey(2)){
					sql  = "update tweetemotions set negative = negative + " + sentimentMap.get(2) + " where trend = '" + trend + "' and date = '" + dateStr + "';";
					stmt.execute(sql);
				}
				if(sentimentMap.containsKey(3)){
					sql  = "update tweetemotions set neutral = neutral + " + sentimentMap.get(3) + " where trend = '" + trend + "' and date = '" + dateStr + "';";
					stmt.execute(sql);
				}
				if(sentimentMap.containsKey(4)){
					sql  = "update tweetemotions set irrelavent = irrelavent + " + sentimentMap.get(4) + " where trend = '" + trend + "' and date = '" + dateStr + "';";
					stmt.execute(sql);
				}
			}else{
				sql = "insert into tweetemotions (trend, date, positive, negative, neutral, irrelavent)" + "value( '"
						+ trend + "', '" + dateStr +"'," + sentimentMap.get(1) + "," + sentimentMap.get(2)  + "," + sentimentMap.get(3) + "," + sentimentMap.get(4) +");";
				try {
					stmt.executeUpdate(sql);
				} catch (SQLException e)
				{
				}
			}
			stmt.close();
			pool.returnConnection(connection);
		} catch (SQLException e) {
		}
	}


	public int[] getEmotionDistribution(String trend, java.util.Date date) {
		String sql = null;
		int[] emotionDistribution = new int[]{0,0,0,0};
		String dateStr = DateUtil.getNewsDateStr(date);
		
		try {
			Connection connection;
			try {
				connection = pool.getConnection();
			} catch (Exception e) {
				return emotionDistribution;
			}
			Statement stmt = connection.createStatement();
			sql  = "select positive,negative,neutral,irrelavent from tweetemotions where date = '" + dateStr + "' and trend = '" + trend +"';";
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				emotionDistribution[0] = rs.getInt(1);
				emotionDistribution[1] = rs.getInt(2);
				emotionDistribution[2] = rs.getInt(3);
				emotionDistribution[3] = rs.getInt(4);
			}
			pool.returnConnection(connection);
		} catch (SQLException e) {
		}
		return emotionDistribution;
	}
}
